 /****** Object:  Stored Procedure USERGetList    Script Date: Monday, June 12, 2006 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spUSERGetNonAdminList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spUSERGetNonAdminList]
GO

		
CREATE PROCEDURE spUSERGetNonAdminList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempUSER (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	US_ID_PK int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempUSER ([US_ID_PK]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [US_ID_PK] FROM [USER] WHERE USER_TYPE > 1 ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [USER] 
WHERE USER_TYPE > 1

SELECT
	[USER].[US_ID_PK],
	[NAME],
	[LOGIN_NAME],
	[PASSWORD],
	[EMAIL],
	[USER_TYPE],
	CountryID,
	[CUSTOMER_CODE],
	[BAR_CODE],
	[IS_ADMIN],
	[IS_ENABLED],
	[CREATED_DATE],
	[CREATED_BY],
	[UPDATED_DATE],
	[UPDATED_BY],
	[CHANGE_PWD_GUID],
	[SORT_REFERENCE1],
	[SORT_REFERENCE2],
	[DOWNLOADABLE_CSV],
	[ActiveOfficeID]
FROM
	#TempUSER AS tblTemp JOIN [USER] ON
	tblTemp.US_ID_PK = [USER].US_ID_PK 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempUSER

GO

	
